package o2o.web.actions.finance;

import com.jinyou.utils.common.*;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import o2o.cache.order.OrderInfoMemory;
import o2o.cache.shop.ShopMemory;
import o2o.cache.user.UserInfoMemory;
import o2o.cache.user.UserOnlineImpl;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.order.ORDER_STATUS;
import o2o.dao.PO.order.ORDER_TYPE;
import o2o.dao.PO.order.OrderInfo;
import o2o.company.model.ShopInfo;
import o2o.dao.PO.user.UserInfo;
import o2o.dao.VO.shop.ShopInfoVO;
import o2o.service.shop.ShopService;
import o2o.system.data.SYS_CONFIG_PATH;
import o2o.system.data.SYS_SETTINGS;
import o2o.task.order.OrderBakTaskUtils;
import o2o.utils.FileUtils;
import o2o.web.actions.shop.ShopMan;
import o2o.web.actions.user.User;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.File;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.*;

import static o2o.web.actions.order.OrderListMan.getShopIdList;

@Controller
public class FinanceAction {

    //    //财务对账  使用sql语句了
//    @RequestMapping(method = RequestMethod.POST, value = "/admin/shop/income/list")
//    @ResponseBody
//    public String device_list(String token, String orderType,
//                              String province, String city, String county, String region,
//                              Integer typeId,
//                              Long shopId,
//                              String content,//店主账号、姓名
//                              Long regionId,
//                              Long agentId, //精确的商圈(等我5分钟在用)
////                              Integer isCityAgent, // //是否按代理获取 0否 1是
//                              Long startTime, Long endTime,
//                              String cityUsername,  // 按城市代理账号获取店铺
//                              Integer isExport, // //是否导出订单 0否 1是
//                              Integer page, Integer size) {
//
//        //验证用户有效性
//        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
//        if (user == null) {
//            return AjaxResult.errorLogoutResult();
//        }
//
//
//        if (!User.hasAdminAuth(user.getUserType())) {
//            return AjaxResult.errorResult("您没有查看权限");
//        }
//
//
//        Integer totalCount = 0;
//        List<ShopInfo> list = null;
//        if (null == shopId) {
//            if (ValidateUtil.isID(agentId)) {
//                list = ShopMan.listOfAgentId(agentId, content);
//            } else {
//                if (ValidateUtil.isNull(city)) {
//                    return AjaxResult.errorResult("请先选择城市");
//                }
//
//                if (ValidateUtil.isID(regionId)) {
//                    list = new ArrayList<ShopInfo>();
//                    List<ShopRegionRelation> relationList = ShopRegionRelationMemory.getInstance().listOfRegionId(regionId);
//                    if (ValidateUtil.isAbsList(relationList)) {
//                        for (int i = 0; i < relationList.size(); i++) {
//                            if (null == relationList.get(i)) {
//                                continue;
//                            }
//                            ShopInfo shopInfo = ShopMemory.getInstance().getShopById(relationList.get(i).getShopId());
//                            list.add(shopInfo);
//                        }
//                    }
//                } else {
//                    list = new ArrayList<ShopInfo>();
//                    List<ShopInfo> tempList = null;
//                    // 按城市代理获取
//                    if (ValidateUtil.isNotNull(cityUsername)) {
////                        tempList = ShopMemory.getInstance().list(city, county, isCityAgent, content, user.getUsername());
//                        // 根据代理账号获取店铺Id
//                        List<Long> shopIdList = getShopIdList(cityUsername);
//
//                        List<ShopInfo> tempv2List = ShopMemory.getInstance().list(typeId, city, county, region, content, false);
//                        if (ValidateUtil.isAbsList(tempv2List)) {
//                            tempList = new ArrayList<>();
//                            // 判断该店铺是否在该代理下
//                            for (int i = 0; i < tempv2List.size(); i++) {
//                                if (null == tempv2List.get(i)) {
//                                    continue;
//                                }
//                                Long shopIdArr = tempv2List.get(i).getId();
//                                if (ValidateUtil.isAbsList(shopIdList)) {
//                                    for (int j = 0; j < shopIdList.size(); j++) {
//                                        if (null == shopIdList.get(j)) {
//                                            continue;
//                                        }
//                                        if (shopIdArr - shopIdList.get(j) == 0) {
//                                            ShopInfo shopInfo = ShopMemory.getInstance().getShopById(shopIdList.get(j));
//                                            if (null == shopInfo) {
//                                                continue;
//                                            }
//                                            tempList.add(shopInfo);
//                                        }
//                                    }
//                                }
//                            }
//                        } else {
//                            return AjaxResult.successResult(0, 0, null);
//                        }
//                    } else {
//                        tempList = ShopMemory.getInstance().list(typeId, city, county, region, content, false);
//                    }
//
//                    if (ValidateUtil.isAbsList(tempList)) {
//                        list.addAll(tempList);
//                    }
//                }
//            }
//
//            if (!ValidateUtil.isAbsList(list)) {
//                return AjaxResult.successResult(0, 0, null);
//            }
//        } else {
//            ShopInfo shopInfo = ShopMemory.getInstance().getShopById(shopId);
//            if (null != shopInfo) {
//                list = new ArrayList<>();
//                list.add(shopInfo);
//                totalCount = 1;
//            }
//        }
//        Integer allShopOrderCount = 0;//所有店铺总订单优惠金额
//        Double allShopPayMoney = 0d;//所有店铺支付总金额
//        Double allShopMoney = 0d;//所有店铺商家总收入
//        Double allShopAwardMoney = 0d; // 所有店铺优惠总金额
//        Double allPlatAwardMoney = 0d; // 所有店铺平台优惠总金额
//
//        Double popOver = 0d;
//        if (ValidateUtil.isAbsList(list)) {
//            List<ShopInfoVO> voList = new ArrayList<ShopInfoVO>();
//            for (int i = 0; i < list.size(); i++) {
//                if (null == list.get(i)) {
//                    continue;
//                }
//                ShopInfoVO shopInfoVO = ShopMan.getBaseOne(null, list.get(i));
//                if (null == shopInfoVO) {
//                    continue;
//                }
//
//                //统计店铺收入
//                List<String> orderNoList = OrderPayMemory.getInstance().listOfShopId(shopInfoVO.getId());
//                if (ValidateUtil.isAbsList(orderNoList)) {
//                    List<OrderPayInfo> payList = new ArrayList<>();
//                    Double totalPayMoney = 0d;//总订单支付金额
//                    Double totalShopMoney = 0d;//店铺总金额
//                    Double platformAwardMoney = 0d; //平台总优惠
//                    Double totalAwardMoney = 0d;//店铺总优惠
//
//                    Double discountsMoney = 0d; // 总优惠费用（平台 + 店铺减）
//                    Double googsPayMoney = 0d;//总商品金额
//                    Double deliveryMoney = 0d;//总配送费
//                    Double packageMoney = 0d;//总打包费
//                    Integer backOrderCount = 0; // 退回订单总数
//                    Double backOrderMoney = 0d;//总订单支付金额
//                    for (int j = 0; j < orderNoList.size(); j++) {
//                        if (null == orderNoList.get(j)) {
//                            continue;
//                        }
//                        OrderPayInfo payInfo = OrderPayMemory.getInstance().get(orderNoList.get(j));
//                        if (null == payInfo) {
//                            continue;
//                        }
//
//                        OrderInfo orderInfo = OrderInfoMemory.getInstance().getOrderInfo(orderNoList.get(j));
//                        if (null == orderInfo) {
//                            continue;
//                        }
//                        if (null != startTime && payInfo.getCreateTime() - startTime < 0) {
//                            continue;
//                        }
//                        if (null != endTime && payInfo.getCreateTime() - endTime > 0) {
//                            continue;
//                        }
//                        // 订单取消不要
//                        if (0 == payInfo.getIsOrderCancel() - 1) {
//                            continue;
//                        }
//                        // 未支付不要
//                        if (0 != 1 - payInfo.getIsPaid()) {
//                            continue;
//                        }
//                        // 申请退款成功的不要
//                        if (0 == 2 - orderInfo.getIsRefundApply()) {
//                            backOrderCount++; // 退回订单数量
//                            backOrderMoney = JYMathDoubleUtils.add(backOrderMoney, orderInfo.getTotalPrice()); // 退回订单总金额
//                            continue;
//                        }
//                        totalPayMoney = JYMathDoubleUtils.add(totalPayMoney, payInfo.getTotalPrice());
//                        totalShopMoney = JYMathDoubleUtils.add(totalShopMoney, payInfo.getShopMoney());
//                        totalShopMoney = JYMathDoubleUtils.round(totalShopMoney, 2);
//
//                        platformAwardMoney = JYMathDoubleUtils.add(platformAwardMoney, payInfo.getPlatformAwardMoney());  // 平台总优惠
//                        totalAwardMoney = JYMathDoubleUtils.add(totalAwardMoney, payInfo.getShopAwardMoney());  // 店铺总优惠
//
//                        // 总优惠费用（平台 + 店铺减）
//                        discountsMoney = JYMathDoubleUtils.add(discountsMoney, orderInfo.getPlatformAwardMoney());
//                        discountsMoney = JYMathDoubleUtils.add(discountsMoney, orderInfo.getShopAwardMoney());
//
//                        googsPayMoney = JYMathDoubleUtils.add(googsPayMoney, orderInfo.getTotalGoodsMoney());// 商品费用
//                        deliveryMoney = JYMathDoubleUtils.add(deliveryMoney, orderInfo.getDeliveryPrice());// 配送费
//                        packageMoney = JYMathDoubleUtils.add(packageMoney, orderInfo.getPacketPrice()); // 打包费
//
//                        payList.add(payInfo);
//                    }
//                    shopInfoVO.setTotalOrderCount(payList.size());//总订单数量
//                    shopInfoVO.setTotalPayMoney(totalPayMoney);//总订单支付金额
//                    shopInfoVO.setTotalShopMoney(totalShopMoney);//店铺总金额
//
//                    shopInfoVO.setPlatformAwardMoney(platformAwardMoney); // 平台总优惠
//                    shopInfoVO.setTotalAwardMoney(totalAwardMoney);//店铺总优惠
//
//                    shopInfoVO.setDiscountsMoney(discountsMoney); // 总优惠费用（平台 + 店铺减）
//                    shopInfoVO.setGoogsPayMoney(googsPayMoney); // 商品费用
//                    shopInfoVO.setDeliveryMoney(deliveryMoney); // 配送费
//                    shopInfoVO.setPackageMoney(packageMoney);   // 打包费
//                    shopInfoVO.setBackOrderCount(backOrderCount);//退回订单数量
//                    shopInfoVO.setBackOrderMoney(backOrderMoney);// 退回订单总金额
//
//
//                    // 所有店铺统计
//                    allShopOrderCount += payList.size();//所有店铺总订单优惠金额
//                    allShopPayMoney += totalPayMoney;//所有店铺支付总金额
//                    allShopMoney += totalShopMoney;//所有店铺商家总收入
//                    allShopAwardMoney += totalAwardMoney; // 所有店铺优惠总金额
//                    allPlatAwardMoney += platformAwardMoney; // 所有店铺平台优惠总金额
//
//                    DecimalFormat df = new DecimalFormat("#.00");
//                    allShopPayMoney = Double.parseDouble(df.format(allShopPayMoney));
//                    allShopMoney = Double.parseDouble(df.format(allShopMoney));
//                    allShopAwardMoney = Double.parseDouble(df.format(allShopAwardMoney));
//                    allPlatAwardMoney = Double.parseDouble(df.format(allPlatAwardMoney));
//                }
//
//                //计已经提现金额
//                UserWallet userWallet = UserWalletMemory.getInstance().getWallet(shopInfoVO.getOwnUsername());
//                if (null != userWallet) {
//                    popOver = userWallet.getPopTotalMoney(); //提现总金额（已到账的）
//                    shopInfoVO.setPopOver(popOver);
//                }
//
////                List<UserWalletPopVO> listV2 = null;
////                StringBuilder sql = new StringBuilder();
////                sql.append("SELECT * FROM ").append(PojoTables.USER_WALLET_POP)
////                        .append(" WHERE delFlag = 0").append(" AND username = '").append(shopInfoVO.getOwnUsername())
////                        .append("' AND isDone = 1 AND userType =1");
////                try {
////                    listV2 = (List<UserWalletPopVO>)UserDaoSql.getInstance().list(UserWalletPopVO.class, sql.toString());
////                } catch (SQLException e) {
////                    e.printStackTrace();
////                    return AjaxResult.errorResult("获取失败，请稍后再试");
////                }
////
////                if (ValidateUtil.isAbsList(listV2)) {
////                    for (int j = 0; j < listV2.size(); j++) {
////                        Double money = listV2.get(j).getMoney();
////                        popOver += money;
////                    }
////                    shopInfoVO.setPopOver(popOver);
////                }
//
//
//                //获取店铺所属行业
//                List<ShopTypeInfoVO> typeInfoVOList = new ArrayList<ShopTypeInfoVO>();
//                List<Integer> typeList = ShopTypeRelationMemory.getInstance().shopTypeList(shopInfoVO.getId());
//                for (int j = 0; j < typeList.size(); j++) {
//                    if (null == typeList.get(j)) {
//                        continue;
//                    }
//
//                    ShopTypeInfo typeInfo = ShopTypeMemory.getInstance().getShopTypeInfoById(Long.parseLong(typeList.get(j) + ""));
//                    ShopTypeInfoVO shopTypeInfoVO = new ShopTypeInfoVO();
//                    if (null != typeInfo) {
//                        try {
//                            ObjectUtils.copyPropertiesExclude(typeInfo, shopTypeInfoVO, null);
//                        } catch (Exception e) {
//                            e.printStackTrace();
//                            continue;
//                        }
//                        if (null == shopTypeInfoVO) {
//                            continue;
//                        }
//                        typeInfoVOList.add(shopTypeInfoVO);
//                    }
//                }
//                shopInfoVO.setShopType(typeInfoVOList);
//
//                List<RegionInfoVO> relationList = ShopMan.getRegionListOfShop(shopInfoVO.getId());
//                if (ValidateUtil.isAbsList(relationList)) {
//                    shopInfoVO.setRegions(relationList);
//                }
//
//                voList.add(shopInfoVO);
//            }
//
//
//            Collections.sort(voList, new Comparator<ShopInfoVO>() {
//                // 先按店铺订单数总数降序，总数相同则按金额排序
//                public int compare(ShopInfoVO arg0, ShopInfoVO arg1) {
//                    if (arg1.getTotalOrderCount().compareTo(arg0.getTotalOrderCount()) != 0) {
//                        //-1 0 1 大于0表示正序，小于0表示逆序
//                        return arg0.getTotalOrderCount().compareTo(arg1.getTotalOrderCount()) > 0 ? -1 : 1;
//                    } else {
//                        return (int) (arg1.getTotalPayMoney() - arg0.getTotalPayMoney());
//                    }
//                }
//            });
//
//            //  订单导出
//            if (null != isExport && isExport - 1 == 0) {
//                String downloadUrl = exportExcel(voList, allShopOrderCount, allShopPayMoney, allShopMoney, allShopAwardMoney, allPlatAwardMoney);
//                if (ValidateUtil.isNotNull(downloadUrl)) {
//                    String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
//                    return AjaxResult.successResult(url);
//                }
//            } else {
//                List<ShopInfo> shopInfoList = null;
//                JYPageUtils.PageList pageList = JYPageUtils.getList(voList, page, size);
//                if (null != pageList && ValidateUtil.isAbsList(pageList.list)) {
//                    shopInfoList = (List<ShopInfo>) pageList.list;
//                    totalCount = pageList.totalCount;
//                }
//
//                HashMap<String, Object> map = new HashMap<>();
//                map.put("status", 1);
//                map.put("totalCount", totalCount);
//                map.put("allShopOrderCount", allShopOrderCount);
//                map.put("allShopPayMoney", allShopPayMoney);
//                map.put("allShopMoney", allShopMoney);
//                map.put("allShopAwardMoney", allShopAwardMoney);
//                map.put("allPlatAwardMoney", allPlatAwardMoney);
//                map.put("data", shopInfoList);
//                if (ValidateUtil.isAbsList(shopInfoList)) {
////                return AjaxResult.successResult(totalCount, shopInfoList.size(), shopInfoList);
//                    return AjaxResult.successResult2(map);
//                }
//            }
//        }
//        return AjaxResult.successResult(0, 0, null);
//    }
    //财务对账
    @RequestMapping(method = RequestMethod.POST, value = "/admin/shop/income/list")
    @ResponseBody
    public String device_list(String token, String orderType,
                              String province, String city, String county, String region,
                              Integer typeId,
                              Long shopId,
                              String content,//店主账号、姓名
                              Long regionId,
                              @RequestParam(defaultValue = "0") Integer isGroupShop,
                              Long agentId, //精确的商圈(等我5分钟在用)
//                              Integer isCityAgent, // //是否按代理获取 0否 1是
                              Long startTime, Long endTime,
                              String cityUsername,  // 按城市代理账号获取店铺
                              Integer isExport, // //是否导出订单 0否 1是
                              Integer page, Integer size) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }


        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有查看权限");
        }

        Long todayDay = endTime != null ? endTime : new Date().getTime();
//        Long secondDay = DateUtils.getStartTime(new Date(), -1);
        Long thirDay = startTime != null ? startTime : DateUtils.getStartTime(new Date(), -2);
        String timeSql = "";
        if (isGroupShop != 1) {
            timeSql = " AND ori.createtime BETWEEN " + thirDay + " AND " + todayDay;
        } else {
            if (ValidateUtil.isAbsLong(startTime)) {
                timeSql += " AND ori.createtime > " + startTime;
            }
            if (ValidateUtil.isAbsLong(endTime)) {
                timeSql += " AND ori.createtime < " + endTime;
            }
        }

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        Integer totalCount = 0;
        String sql = "SELECT ori.shopName,ori.shopId, " +
                "SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN 1 ELSE 0 END) totalOrderCount, " +
                "SUM(CASE WHEN ori.orderStatus = 9 AND ori.isRefundApply!=2 AND orp.shopAwardMoney THEN orp.shopAwardMoney ELSE 0 END) totalAwardMoney,  " +
                "SUM(CASE WHEN ori.orderStatus = 9 AND ori.isRefundApply!=2 AND orp.platformAwardMoney THEN orp.platformAwardMoney ELSE 0 END) platformAwardMoney,  " +
                "SUM(CASE WHEN ori.orderStatus = 9 AND ori.isRefundApply!=2 AND ori.deliveryPrice THEN ori.deliveryPrice ELSE 0 END) deliveryMoney,  " +
                "SUM(CASE WHEN ori.orderStatus = 9 AND ori.isRefundApply!=2 AND ori.packetPrice THEN ori.packetPrice ELSE 0 END) packageMoney,  " +
                "SUM(CASE WHEN ori.isRefundApply=2 or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN 1 ELSE 0 END) backOrderCount,  " +
                "SUM(CASE WHEN ori.isRefundApply=2 or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN ori.totalPrice ELSE 0 END) backOrderMoney,  " +
                "SUM(CASE WHEN ori.orderStatus = 9 AND ori.isRefundApply!=2 AND ori.totalGoodsMoney THEN ori.totalGoodsMoney ELSE 0 END) googsPayMoney,  " +
                "SUM(CASE WHEN ori.orderStatus = 9 AND ori.isRefundApply!=2 AND orp.totalPrice THEN orp.totalPrice ELSE 0 END) totalPayMoney,  " +
                "SUM(CASE WHEN ori.orderStatus = 9 AND ori.isRefundApply!=2 AND orp.shopMoney THEN orp.shopMoney ELSE 0 END) totalShopMoney, " +
                "(CASE WHEN uw.popTotalMoney THEN uw.popTotalMoney ELSE 0 END) popOver " +
                "FROM " + t_order_info + " ori  " +
                "LEFT JOIN " + t_order_pay + " orp ON ori.orderNo = orp.orderNo " +
                "LEFT JOIN " + PojoTables.ShopInfo + " AS spo ON spo.id=ori.shopId and spo.delFlag =0 " +
                "LEFT JOIN " + PojoTables.USER_WALLET + " uw ON uw.shopId = ori.shopId and uw.delFlag =0 " +
                (agentId != null || StringUtils.isNotEmpty(cityUsername) ? ("LEFT JOIN " + PojoTables.SHOP_AGENT_RELATION + " sar ON sar.shopId = spo.id and sar.delFlag =0 ") : "") +
                (StringUtils.isNotEmpty(cityUsername) ? "LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " uar ON uar.agentId = sar.agentId and uar.delFlag =0 " : "") +
                "WHERE ori.delFlag=0  AND ori.shopId > 0 AND spo.delFlag = 0 " +
                (agentId != null ? (" AND sar.delFlag=0 ") : "") +
                (StringUtils.isNotEmpty(cityUsername) ? " AND uar.delFlag=0" : "") +
                (StringUtils.isNotEmpty(city) ? (" AND spo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND spo.county='" + county + "'") : "") +
                (StringUtils.isNotEmpty(cityUsername) ? (" AND uar.username = '" + cityUsername + "'") : "") +
                (agentId != null ? (" AND sar.agentId = " + agentId + "'") : "") +
                (StringUtils.isNotEmpty(content) ? (" AND (spo.ownUsername='" + content + "' or spo.shopName like '%" + content + "%')") : "") +
                (shopId != null ? (" AND ori.shopId='" + shopId + "'") : "") +
                (isGroupShop == 0 ? (" AND ori.orderType !=" + ORDER_TYPE.GROUP_ORDER) : (" AND ori.orderType =" + ORDER_TYPE.GROUP_ORDER)) +
//                " AND ori.createtime BETWEEN " + thirDay + " AND " + todayDay +
                timeSql +
                " GROUP BY ori.shopId" +
                " order by totalOrderCount desc";

        List<HashMap<String, Object>> list = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        Integer allShopOrderCount = 0;//所有店铺总订单优惠金额
        Double allShopPayMoney = 0d;//所有店铺支付总金额
        Double allShopMoney = 0d;//所有店铺商家总收入
        Double allShopAwardMoney = 0d; // 所有店铺优惠总金额
        Double allPlatAwardMoney = 0d; // 所有店铺平台优惠总金额

        Double popOver = 0d;
        if (ValidateUtil.isAbsList(list)) {
            for (int i = 0; i < list.size(); i++) {
                if (null == list.get(i)) {
                    continue;
                }

                // 所有店铺统计
                allShopOrderCount += Integer.parseInt(list.get(i).get("totalOrderCount").toString()); //所有店铺总订单优惠金额
                allShopPayMoney += Double.parseDouble(list.get(i).get("totalPayMoney").toString());//所有店铺支付总金额
                allShopMoney += Double.parseDouble(list.get(i).get("totalShopMoney").toString());//所有店铺商家总收入
                allShopAwardMoney += Double.parseDouble(list.get(i).get("totalAwardMoney").toString()); // 所有店铺优惠总金额
                allPlatAwardMoney += Double.parseDouble(list.get(i).get("platformAwardMoney").toString()); // 所有店铺平台优惠总金额
            }

            DecimalFormat df = new DecimalFormat("#.00");
            allShopPayMoney = Double.parseDouble(df.format(allShopPayMoney));
            allShopMoney = Double.parseDouble(df.format(allShopMoney));
            allShopAwardMoney = Double.parseDouble(df.format(allShopAwardMoney));
            allPlatAwardMoney = Double.parseDouble(df.format(allPlatAwardMoney));

            //  订单导出
            if (null != isExport && isExport - 1 == 0) {
                String downloadUrl = exportExcel(list, allShopOrderCount, allShopPayMoney, allShopMoney, allShopAwardMoney, allPlatAwardMoney);
                if (ValidateUtil.isNotNull(downloadUrl)) {
                    if(ValidateUtil.isNotNetUrl(downloadUrl)){
                        downloadUrl = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                    }
                    return AjaxResult.successResult(downloadUrl);
                }
            } else {
                List<ShopInfo> shopInfoList = null;
                JYPageUtils.PageList pageList = JYPageUtils.getList(list, page, size);
                if (null != pageList && ValidateUtil.isAbsList(pageList.list)) {
                    shopInfoList = (List<ShopInfo>) pageList.list;
                    totalCount = pageList.totalCount;
                }

                HashMap<String, Object> map = new HashMap<>();
                map.put("status", 1);
                map.put("totalCount", totalCount);
                map.put("allShopOrderCount", allShopOrderCount);
                map.put("allShopPayMoney", allShopPayMoney);
                map.put("allShopMoney", allShopMoney);
                map.put("allShopAwardMoney", allShopAwardMoney);
                map.put("allPlatAwardMoney", allPlatAwardMoney);
                map.put("data", shopInfoList);
                if (ValidateUtil.isAbsList(shopInfoList)) {
//                return AjaxResult.successResult(totalCount, shopInfoList.size(), shopInfoList);
                    return AjaxResult.successResult2(map);
                }
            }
        }
        return AjaxResult.successResult(0, 0, null);
    }

    //订单统计
    @RequestMapping(method = RequestMethod.POST, value = "/admin/shop/statistics/list")
    @ResponseBody
    public String order_statistics_list(String token, String orderType,
                                        String province, String city, String county, String region,
                                        Integer typeId,
                                        Long shopId,
                                        String content,//店主账号、姓名
                                        Long regionId,
                                        Long agentId, //精确的商圈(等我5分钟在用)
                                        Long startTime, Long endTime,
                                        String cityUsername,  // 按城市代理账号获取店铺
                                        Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }


        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有查看权限");
        }

        //todo 1.根据城市代理，店铺Id, 根据店铺名称获取all店铺
        Integer totalCount = 0;
        List<ShopInfo> list = null;
        if (ValidateUtil.isNotID(shopId)) {
            if (ValidateUtil.isID(agentId)) {
                list = ShopMan.listOfAgentId(agentId, content);
            } else {
                if (ValidateUtil.isNull(city)) {
                    return AjaxResult.errorResult("请先选择城市");
                }
                list = new ArrayList<ShopInfo>();
                List<ShopInfo> tempList = null;
                // 按城市代理获取店铺
                if (ValidateUtil.isNotNull(cityUsername)) {
                    List<Long> shopIdList = getShopIdList(cityUsername);
                    // List<ShopInfo> tempv2List = ShopMemory.getInstance().list(typeId, city, county, region, content, false);
                    List<ShopInfo> tempv2List = ShopMemory.getInstance().list(true, typeId, city, county, region, content, false, false, false, false);
                    if (ValidateUtil.isAbsList(tempv2List)) {
                        tempList = new ArrayList<>();
                        // 判断该店铺是否在该代理下
                        for (int i = 0; i < tempv2List.size(); i++) {
                            if (null == tempv2List.get(i)) {
                                continue;
                            }
                            Long shopIdArr = tempv2List.get(i).getId();
                            if (ValidateUtil.isAbsList(shopIdList)) {
                                for (int j = 0; j < shopIdList.size(); j++) {
                                    if (null == shopIdList.get(j)) {
                                        continue;
                                    }
                                    if (shopIdArr - shopIdList.get(j) == 0) {
                                        ShopInfo shopInfo = ShopMemory.getInstance().getShopById(shopIdList.get(j));
                                        if (null == shopInfo) {
                                            continue;
                                        }
                                        tempList.add(shopInfo);
                                    }
                                }
                            }
                        }
                    } else {
                        return AjaxResult.successResult(0, 0, null);
                    }
                } else {
                    // tempList = ShopMemory.getInstance().list(typeId, city, county, region, content, false);
                    tempList = ShopMemory.getInstance().list(true, typeId, city, county, region, content, false, false, false, false);
                }

                if (ValidateUtil.isAbsList(tempList)) {
                    list.addAll(tempList);
                }
            }
            if (!ValidateUtil.isAbsList(list)) {
                return AjaxResult.successResult(0, 0, null);
            }
        } else {
            ShopInfo shopInfo = ShopMemory.getInstance().getShopById(shopId);
            if (null != shopInfo) {
                list = new ArrayList<>();
                list.add(shopInfo);
                totalCount = 1;
            }
        }
        //todo 2.根据all店铺Id获取符合条件的订单
        if (ValidateUtil.isAbsList(list)) {
            List<ShopInfoVO> voList = new ArrayList<ShopInfoVO>();
            for (int i = 0; i < list.size(); i++) {
                if (null == list.get(i)) {
                    continue;
                }
                ShopInfoVO shopInfoVO = ShopService.getInstance().getShopInfoVoDetailOne(null, list.get(i));
                if (null == shopInfoVO) {
                    continue;
                }

                List<String> orderNoList = OrderInfoMemory.getInstance().listOfShopId(list.get(i).getId());
                if (ValidateUtil.isAbsList(orderNoList)) {
                    List<OrderInfo> orderInfoList = new ArrayList<OrderInfo>();
                    // 该店铺下
                    Double googsPayMoney = 0d;//总商品支付金额
                    Double deliveryMoney = 0d;//总配送费
                    Double packageMoney = 0d;//总打包费
                    Double discountsMoney = 0d;//总优惠费用（平台和店铺减）
                    Double totalPayMoney = 0d;//总订单支付金额
                    Integer backOrderCount = 0; // 退回订单总数
                    Double backOrderMoney = 0d;//总订单支付金额

                    Double redPacMoney = 0d;//总红包费用
                    for (int j = 0; j < orderNoList.size(); j++) {
                        if (null == orderNoList.get(j)) {
                            continue;
                        }
                        //todo 3.根据all订单获取需要的数据信息
                        OrderInfo orderInfo = OrderInfoMemory.getInstance().getOrderInfo(orderNoList.get(j));
                        if (null == orderInfo) {
                            continue;
                        }

                        if (null != startTime && orderInfo.getCreateTime() - startTime < 0) {
                            continue;
                        }
                        if (null != endTime && orderInfo.getCreateTime() - endTime > 0) {
                            continue;
                        }

                        // 未支付不要
                        if (0 != 1 - orderInfo.getIsPaid()) {
                            continue;
                        }
                        // 申请退款成功的不要
                        if (0 == 2 - orderInfo.getIsRefundApply()) {
                            backOrderCount++; // 退回订单数量
                            backOrderMoney = JYMathDoubleUtils.add(backOrderMoney, orderInfo.getTotalPrice()); // 退回订单总金额
                            continue;
                        }
                        googsPayMoney = JYMathDoubleUtils.add(googsPayMoney, orderInfo.getTotalGoodsMoney());
                        deliveryMoney = JYMathDoubleUtils.add(deliveryMoney, orderInfo.getDeliveryPrice());
                        packageMoney = JYMathDoubleUtils.add(packageMoney, orderInfo.getPacketPrice());

                        // 总优惠费用（平台 + 店铺减）
                        discountsMoney = JYMathDoubleUtils.add(discountsMoney, orderInfo.getPlatformAwardMoney());
                        discountsMoney = JYMathDoubleUtils.add(discountsMoney, orderInfo.getShopAwardMoney());
                        totalPayMoney = JYMathDoubleUtils.add(totalPayMoney, orderInfo.getTotalPrice());
                        orderInfoList.add(orderInfo);
                    }

                    shopInfoVO.setTotalOrderCount(orderInfoList.size());
                    shopInfoVO.setGoogsPayMoney(googsPayMoney); // 商品费用
                    shopInfoVO.setDeliveryMoney(deliveryMoney); // 配送费
                    shopInfoVO.setPackageMoney(packageMoney);   // 打包费
                    shopInfoVO.setDiscountsMoney(discountsMoney); // 总优惠费用（平台 + 店铺减）
                    shopInfoVO.setTotalPayMoney(totalPayMoney);//总订单支付金额

                    shopInfoVO.setBackOrderCount(backOrderCount);//退回订单数量
                    shopInfoVO.setBackOrderMoney(backOrderMoney);//总订单支付金额
                }
                voList.add(shopInfoVO);
            }
            List<ShopInfo> shopInfoList = null;
            JYPageUtils.PageList pageList = JYPageUtils.getList(voList, page, size);
            if (null != pageList && ValidateUtil.isAbsList(pageList.list)) {
                shopInfoList = (List<ShopInfo>) pageList.list;
                totalCount = pageList.totalCount;
            }
            if (ValidateUtil.isAbsList(shopInfoList)) {
                return AjaxResult.successResult(totalCount, shopInfoList.size(), shopInfoList);
            }
        }
        return AjaxResult.successResult(0, 0, null);

    }


    // 财务对账（对跑腿，代购，帮办）
    @RequestMapping(method = RequestMethod.POST, value = "/admin/money/addUp/list")
    @ResponseBody
    public String money_check(String token, String orderType,
                              String city,
                              Long agentId,
                              Long startTime, Long endTime
    ) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (ValidateUtil.isNull(city)) {
            return AjaxResult.errorResult("城市不能为空");
        }

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT a.orderType,COUNT(*) AS toatalCount, SUM(b.deliveryPrice) AS deliveryPrice, SUM(b.xiaofei) AS xiaofei, SUM(b.goodsPrice) AS goodsPrice, SUM(b.weightPrice) AS weightPrice" +
                ", SUM(b.yunfeixianPrice) AS yunfeixianPrice, SUM(b.specialPrice) AS specialPrice  from ")
                .append(t_order_info).append(" a LEFT JOIN ").append(PojoTables.OTHER_ORDER_INFO).append(" b ON a.orderNo = b.orderNo")
                .append(" where b.orderNo IS NOT NULL and b.toCity='" + city).append("'").append(ValidateUtil.isNotID(agentId) ? "" : "AND a.agentId =" + agentId)
                .append(ValidateUtil.isNull(orderType) ? "" : "AND orderType" + orderType)
                .append(" GROUP BY orderType");
        List<HashMap<String, Object>> list = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sb.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return AjaxResult.successResult(list.size(), list);
    }

    // ===================
    // 导excel
    private String exportExcel(List<HashMap<String, Object>> list, Integer allShopOrderCount, Double allShopPayMoney, Double allShopMoney, Double allShopAwardMoney, Double allPlatAwardMoney) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

            WritableSheet sheet = workbook.createSheet("sheet1", 0);

            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式

            int col2 = 0;   // 列
            sheet.addCell(new Label(col2++, 0, "总计"));
            sheet.addCell(new Label(col2++, 1, "订单总数"));
            sheet.addCell(new Label(col2++, 1, allShopOrderCount + ""));
            sheet.addCell(new Label(col2++, 1, "支付总金额"));
            sheet.addCell(new Label(col2++, 1, allShopPayMoney + ""));
            sheet.addCell(new Label(col2++, 1, "平台优惠总金额"));
            sheet.addCell(new Label(col2++, 1, allPlatAwardMoney + ""));
            sheet.addCell(new Label(col2++, 1, "店铺优惠总金额"));
            sheet.addCell(new Label(col2++, 1, allShopAwardMoney + ""));
            sheet.addCell(new Label(col2++, 1, "商家总收入"));
            sheet.addCell(new Label(col2++, 1, allShopMoney + ""));

            int col = 0;   // 列
            int row = 3;  // 行

            sheet.addCell(new Label(col++, row, "商家名称"));
            sheet.addCell(new Label(col++, row, "已完成订单(数量)"));
            sheet.addCell(new Label(col++, row, "商家优惠金额"));
            sheet.addCell(new Label(col++, row, "平台优惠金额"));
            sheet.addCell(new Label(col++, row, "配送费"));

            sheet.addCell(new Label(col++, row, "打包费"));
            sheet.addCell(new Label(col++, row, "退款订单数量"));
            sheet.addCell(new Label(col++, row, "退款金额"));
            sheet.addCell(new Label(col++, row, "商品金额"));
            sheet.addCell(new Label(col++, row, "支付金额"));
            sheet.addCell(new Label(col++, row, "商家收入"));

            row = 4;
            for (Map<String, Object> record : list) {
                col = 0;
                sheet.addCell(new Label(col++, row, record.get("shopName").toString()));
                sheet.addCell(new Label(col++, row, record.get("totalOrderCount").toString() + ""));
                sheet.addCell(new Label(col++, row, record.get("totalAwardMoney").toString() + ""));
                sheet.addCell(new Label(col++, row, record.get("platformAwardMoney").toString() + ""));
                sheet.addCell(new Label(col++, row, record.get("deliveryMoney").toString() + ""));
                sheet.addCell(new Label(col++, row, record.get("packageMoney").toString() + ""));
                sheet.addCell(new Label(col++, row, record.get("backOrderCount").toString() + ""));
                sheet.addCell(new Label(col++, row, record.get("backOrderMoney").toString() + ""));
                sheet.addCell(new Label(col++, row, record.get("googsPayMoney").toString() + ""));
                sheet.addCell(new Label(col++, row, record.get("totalPayMoney").toString() + ""));
                sheet.addCell(new Label(col++, row, record.get("totalShopMoney").toString() + ""));
                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            outputPath = JYFileUtils.saveFile2Oss(outputPath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }


}
